/*
 * NewRouteCommand.java
 *
 * Created on October 5, 2007, 12:00 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

/*
 * SQL Query useful for testing the results:
 *
 * 
   SELECT floor(departure/60/60) % 24 AS dep_hour, 
        floor(departure/60) % 60 AS dep_min, 
        floor(arrival/60) % 60 AS arr_min, 
        floor(arrival/60/60) % 24 AS arr_hour 
   FROM schedule WHERE schedule_id=26 # Replace ID with whatever schedule you want to see the times for
 *
 *
 */

package drb;

import java.io.BufferedReader;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Asger
 */
public class NewRouteCommand extends PromptingCommand {
    
    /** Creates a new instance of NewRouteCommand */
    public NewRouteCommand(BufferedReader input) {
        super(input);
    }
    
    private static final String[] Quantifiers = new String[]
    {
        "first", "second", "third", "fourth", "fifth", "sixth"
    };
    
    public void execute() throws SQLException {
        
        System.out.println("-------------------------------");
        System.out.println("First we enter the station names in the route (max. 6). Leave blank to end route.");
        
        // Get the list of stations in the new route
        ArrayList<String> stations = new ArrayList<String>();
        while (stations.size() < 6)
        {
            int num = stations.size();
            String stationName = promptStation("Please enter the " + Quantifiers[num] + " station name:", connection, true);
            if (stationName.length() == 0)
            {
                if (stations.size() < 2)
                    System.err.println("You should enter at least two stations.");
                else
                    break;
            }
            else
            {
                stations.add(stationName);
            }
        }
        
        String conStr = stations.get(0);
        for (int i=1; i<stations.size(); i++) {
            conStr += "-" + stations.get(i);
        }
        
        System.out.println("-------------------------------");
        System.out.println("Connection: " + conStr);
        
        // Get the departure/arrival times
        ArrayList<Long> times = new ArrayList<Long>();
        long start = promptTime("When should the train depart from " + stations.get(0) + "?");
        times.add(start - ONE_MINUTE);
        for (int i=1; i<stations.size(); i++) {
            long time = -1;
            while (time == -1)
            {
                String remark = (i == stations.size()-1)? "":" (The train will depart one minute later)";
                time = promptTime("When should the train arrive at " + stations.get(i) + "?" + remark);
                
                while (time < times.get(i-1) + ONE_MINUTE) {
                    time += ONE_DAY; // Support times spanning across midnight
                }
            }
            
            times.add(time);
        }
        
        // Get the week days
        System.out.println("Please enter which weekdays the route should be active: (Default is: Mon,Tue,Wed,Thu,Fri,Sat,Sun)");
        String weekdays = promptWeekDays("Mon,Tue,Wed,Thu,Fri,Sat,Sun");
        
        // Get standard/bed wagon type
        boolean bedWagons = promptYesNo("Should the route use bed-wagons (instead of standard wagons)?", false);
        char wagonType = bedWagons? 'B':'S';
        
        // Get the number of wagons to use on this route
        System.out.println("How many wagons should be on this route?");
        int numWagons = promptPositiveInt();
        
        System.out.println("How many of those should be first class wagons? (0-" + numWagons+") (The rest will be economic class)");
        int fcWagons = promptInt(0, numWagons);
        
        int ecoWagons = numWagons - fcWagons;
        
        int ecoPrice = 0;
        int fcPrice = 0;
        
        if (ecoWagons > 0)
        {
            System.out.println("How much should an economic class ticket cost?");
            ecoPrice = promptPositiveInt();
        }
        if (fcWagons > 0)
        {
            System.out.println("How much should a first class ticket cost?");
            fcPrice = promptPositiveInt();
        }
        
        connection.setAutoCommit(false);
        
        // Get a new schedule ID (inside the transaction!!)
        String sql = "SELECT MAX(schedule_id) FROM schedule";
        ResultSet result = connection.createStatement().executeQuery(sql);
        result.next();
        int id = result.getInt(1) + 1;
        
        // Finally! Time to add the schedules!
        for (int i=0; i<stations.size()-1; i++)
        {
            String from = stations.get(i);
            String to = stations.get(i+1);
            long departure = times.get(i) + ONE_MINUTE;
            long arrival = times.get(i+1);
            
            sql = "INSERT INTO schedule (schedule_id, departure, arrival, from_name, to_name, eco_wagons, fc_wagons, week_days, eco_price, fc_price, wagon_type) VALUES " +
                    "("+id+", "+departure+", "+arrival+", '"+escapeSQL(from)+"', '"+escapeSQL(to)+"', "+ecoWagons+", "+fcWagons+", '"+escapeSQL(weekdays)+"', " +
                    ""+ecoPrice+", "+fcPrice+", '"+wagonType+"')";
            connection.createStatement().execute(sql);
        }
        
        connection.commit();
        connection.setAutoCommit(true);
    }
}










